Continuous writing/reading to MDB

[Jeff Pritchard]

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a network
into the MDB on the server at the rate of about 120 records a minute. This
may increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be
writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing and
having a corrupted MDB. The issue of speed is another. We expect 70-80,000
records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day and
start empty the next day.

I don't see any locking issues.

Jeff

[Larry Linson]

When you are working with a file-server database, this is always a
consideration. Whether you should consider alternatives would depend on how
"mission-critical" this is -- that is, what would it cost your company for
it to be out of service while you Compact/Repair? What would it cost if you
couldn't successfully repair, and had to restore from a backup? How would
you "catch up" the updates from the backup to current?

You may tell management and they may decide it's not "all that big a deal"
and to go with the file-server (Jet) database; or you may hear a sharp
intake of breath from management. Have the comparison costs of full retail
SQL Server ready so they can see how many "hits" it would take to pay pack.
I have worked on a few databases that could have been done with file server
from a performance/user audience size point of view, but were done client
server because of their importance to the operation and the superior
reliability and recoverability of the server DB.

Larry Linson
Microsoft Access MVP

"Jeff Pritchard" <je************@asken.com.au> wrote in message
news:8H***************@news.optus.net.au...

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a network into the MDB on the server at the rate of about 120 records a minute. This
may increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be
writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing and having a corrupted MDB. The issue of speed is another. We expect 70-80,000
records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day and
start empty the next day.

I don't see any locking issues.

Jeff

[Tom van Stiphout]

On Sat, 15 Nov 2003 01:54:12 GMT, "Jeff Pritchard"
<je************@asken.com.au> wrote:

A peak append rate of 3 - 5 records (or higher?) per second is rather
high. I would take an hour or so to write a simple proof-of-concept
app to make sure your system can handle that. Tick a timer every 200
msec and append a row. Then add 100,000 rows to that table and try
again.

I'm not that concerned about the SELECT queries. The power of indexing
should ensure it hardly matters whether you're querying 100 rows or
100,000. Except for some increased network traffic. You could even
have those guys hitting a separate database, which you populate every
so often with a data dump from the first database.

Access IMHO is not a 24/7 database. I would lean towards a SQL Server
database for this.

-Tom.

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a network
into the MDB on the server at the rate of about 120 records a minute. This
may increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be
writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing and
having a corrupted MDB. The issue of speed is another. We expect 70-80,000
records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day and
start empty the next day.

I don't see any locking issues.

Jeff

[Jeff Pritchard]

Hi Larry

Given that the data is archived each day, there is only the possibility of
losing a days data. The data is for monitoring weights only. They have run
for years without this and their approach is that it is not mission
critical. While not ideal to lose a days data, it is not going to stop
production.

We would only compact when it is archived. Even then probably not necessary
as we would simply replace the MDB with an empty copy.

The thought of setting up MSDE on the workstation was raised but then they
can't legally access this from multiple workstations, I think.

Do you think there will be a problem with others querying while data is
being continually written?

Jeff
"Larry Linson" <bo*****@localhost.not> wrote in message
news:hp*****************@nwrddc02.gnilink.net...

When you are working with a file-server database, this is always a
consideration. Whether you should consider alternatives would depend on how "mission-critical" this is -- that is, what would it cost your company for
it to be out of service while you Compact/Repair? What would it cost if you couldn't successfully repair, and had to restore from a backup? How would
you "catch up" the updates from the backup to current?

You may tell management and they may decide it's not "all that big a deal"
and to go with the file-server (Jet) database; or you may hear a sharp
intake of breath from management. Have the comparison costs of full retail
SQL Server ready so they can see how many "hits" it would take to pay pack. I have worked on a few databases that could have been done with file server from a performance/user audience size point of view, but were done client
server because of their importance to the operation and the superior
reliability and recoverability of the server DB.

Larry Linson
Microsoft Access MVP

"Jeff Pritchard" <je************@asken.com.au> wrote in message
news:8H***************@news.optus.net.au...

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a

network

into the MDB on the server at the rate of about 120 records a minute. This may increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing

and

having a corrupted MDB. The issue of speed is another. We expect 70-80,000 records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day and start empty the next day.

I don't see any locking issues.

Jeff

[Tom van Stiphout]

On Sat, 15 Nov 2003 05:13:49 GMT, "Jeff Pritchard"
<je************@asken.com.au> wrote:

You may want to read up on MSDE. It is a server database, not a client
one. It can be accessed by multiple users at the same time. It is
tuned for 5 concurrent users - above that number it will put requests
in a queue. You can likely get away with 1 writer and 10 - 15
occasional readers without too much of a problem.

-Tom.

Hi Larry

Given that the data is archived each day, there is only the possibility of
losing a days data. The data is for monitoring weights only. They have run
for years without this and their approach is that it is not mission
critical. While not ideal to lose a days data, it is not going to stop
production.

We would only compact when it is archived. Even then probably not necessary
as we would simply replace the MDB with an empty copy.

The thought of setting up MSDE on the workstation was raised but then they
can't legally access this from multiple workstations, I think.

Do you think there will be a problem with others querying while data is
being continually written?

Jeff

<clip>

[Jeff Pritchard]

Hi Tom

Yes I realise MSDE is multi-user, just wasn't sure about the number of users
you could legally have accessing it. Isn't there a legal limit?

The practical limit is another issue. The 10-15 readers is probably okay at
the moment. Definitely would only be one writer.

Jeff

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:pe********************************@4ax.com...

On Sat, 15 Nov 2003 05:13:49 GMT, "Jeff Pritchard"
<je************@asken.com.au> wrote:

You may want to read up on MSDE. It is a server database, not a client
one. It can be accessed by multiple users at the same time. It is
tuned for 5 concurrent users - above that number it will put requests
in a queue. You can likely get away with 1 writer and 10 - 15
occasional readers without too much of a problem.

-Tom.

Hi Larry

Given that the data is archived each day, there is only the possibility oflosing a days data. The data is for monitoring weights only. They have runfor years without this and their approach is that it is not mission
critical. While not ideal to lose a days data, it is not going to stop
production.

We would only compact when it is archived. Even then probably not necessaryas we would simply replace the MDB with an empty copy.

The thought of setting up MSDE on the workstation was raised but then theycan't legally access this from multiple workstations, I think.

Do you think there will be a problem with others querying while data is
being continually written?

Jeff

<clip>

[Jeff Pritchard]

Good suggestion. I will set up a database on my peer-to-peer and test it at
different rates.

I doubt they would ever get higher that 3, 4 at most. That is probably at
least a couple of years away as their manufacturing equip can't do that at
the moment. If that happens we can revisit the problem and maybe make some
changes then.

Jeff

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:pv********************************@4ax.com...

On Sat, 15 Nov 2003 01:54:12 GMT, "Jeff Pritchard"
<je************@asken.com.au> wrote:

A peak append rate of 3 - 5 records (or higher?) per second is rather
high. I would take an hour or so to write a simple proof-of-concept
app to make sure your system can handle that. Tick a timer every 200
msec and append a row. Then add 100,000 rows to that table and try
again.

I'm not that concerned about the SELECT queries. The power of indexing
should ensure it hardly matters whether you're querying 100 rows or
100,000. Except for some increased network traffic. You could even
have those guys hitting a separate database, which you populate every
so often with a data dump from the first database.

Access IMHO is not a 24/7 database. I would lean towards a SQL Server
database for this.

-Tom.

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a networkinto the MDB on the server at the rate of about 120 records a minute. Thismay increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be
writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing andhaving a corrupted MDB. The issue of speed is another. We expect 70-80,000records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day andstart empty the next day.

I don't see any locking issues.

Jeff

[Larry Linson]

"Jeff Pritchard" wrote

Yes I realise MSDE is multi-user, just
wasn't sure about the number of users
you could legally have accessing it. Isn't
there a legal limit?
No, there is not. It enforces its intended use by inserting delays on each
(internal) "batch update process" above 5. If you add 'way too many users,
then performance will deteriorate to the point that you will give up in
disgust and not add any more. In earlier versions, it would only _perform_ 5
of those operations at a time, and under those conditions, I have seen
reliable reports of 25 concurrent users. I haven't seen similar reports for
the current version with its modified approach to user limits.
The practical limit is another issue. The
10-15 readers is probably okay at
the moment. Definitely would only be
one writer.

Don't take Tom's numbers as any kind of "absolute limit" -- performance will
depend on many factors, including the environment and the requirements,
design, and implementation. With experience, I am sure you can tweak better
performance for more users than you will get initially.

On the other hand, I am quite comfortable that Tom is correct that, in your
planned environment, MSDE would likely be more than adequate. And, if you
grow to the point that it is not, it is easy to replace it with Microsoft
SQL Server.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

je************@asken.com.au (Jeff Pritchard) wrote in
<8H***************@news.optus.net.au>:

I have an MDB that contains a single table. Checkweigher data is
being continuously written to this MDB from a dedicated
workstation over a network into the MDB on the server at the rate
of about 120 records a minute. This may increase in the future to
about 200. The records are not large.

We have other workstations that run sessions on Terminal Server
querying this MDB, doing things like plotting the last hours data
on a graph or running other reports, or even just viewing raw
data. No one else will be writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the
writing and having a corrupted MDB. The issue of speed is another.
We expect 70-80,000 records a day which means that there could be
a substantial delay in querying towards the end of the day. The
MDB will be archived each day and start empty the next day.

Well, you could eliminate the network connection problem by putting
the data file on the same machine where the writes are being
initiated, then let the WTS users connect to it from the terminal
server. Since they are read-only, loss of network connection
wouldn't be a problem.

If you can't give network access to this workstation, then perhaps
replication would work, with a replica on your WTS machine,
synchronized on a schedule. Unfortunately, Replication Manager
can't synch more often than every 15 minutes. I don't know if
Michael Kaplan's TSI tool could be used to initiate a
synchronization more frequently.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jeff Pritchard]

Putting the MDB on the workstation may be a go. I will keep this in mind as
we progress.

Jeff

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .

je************@asken.com.au (Jeff Pritchard) wrote in
<8H***************@news.optus.net.au>:

I have an MDB that contains a single table. Checkweigher data is
being continuously written to this MDB from a dedicated
workstation over a network into the MDB on the server at the rate
of about 120 records a minute. This may increase in the future to
about 200. The records are not large.

We have other workstations that run sessions on Terminal Server
querying this MDB, doing things like plotting the last hours data
on a graph or running other reports, or even just viewing raw
data. No one else will be writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the
writing and having a corrupted MDB. The issue of speed is another.
We expect 70-80,000 records a day which means that there could be
a substantial delay in querying towards the end of the day. The
MDB will be archived each day and start empty the next day.

Well, you could eliminate the network connection problem by putting
the data file on the same machine where the writes are being
initiated, then let the WTS users connect to it from the terminal
server. Since they are read-only, loss of network connection
wouldn't be a problem.

If you can't give network access to this workstation, then perhaps
replication would work, with a replica on your WTS machine,
synchronized on a schedule. Unfortunately, Replication Manager
can't synch more often than every 15 minutes. I don't know if
Michael Kaplan's TSI tool could be used to initiate a
synchronization more frequently.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jeff Pritchard]

Okay. Thanks for your comments. I am better armed for the battle now.

MSDE is a definite option if the MDB solution has a problem. I think we will
start with the MDB on the server (clients preference). If problems occur
moving it to the workstation may be the go, then MSDE.

Jeff

"Larry Linson" <bo*****@localhost.not> wrote in message
news:Si******************@nwrddc02.gnilink.net...

"Jeff Pritchard" wrote

> Yes I realise MSDE is multi-user, just
> wasn't sure about the number of users
> you could legally have accessing it. Isn't
> there a legal limit?
No, there is not. It enforces its intended use by inserting delays on each
(internal) "batch update process" above 5. If you add 'way too many users,
then performance will deteriorate to the point that you will give up in
disgust and not add any more. In earlier versions, it would only _perform_

5 of those operations at a time, and under those conditions, I have seen
reliable reports of 25 concurrent users. I haven't seen similar reports for the current version with its modified approach to user limits.

> The practical limit is another issue. The
> 10-15 readers is probably okay at
> the moment. Definitely would only be
> one writer.
Don't take Tom's numbers as any kind of "absolute limit" -- performance

will depend on many factors, including the environment and the requirements,
design, and implementation. With experience, I am sure you can tweak better performance for more users than you will get initially.

On the other hand, I am quite comfortable that Tom is correct that, in your planned environment, MSDE would likely be more than adequate. And, if you
grow to the point that it is not, it is easy to replace it with Microsoft
SQL Server.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"Jeff Pritchard" wrote

MSDE is a definite option if the MDB
solution has a problem. I think we will
start with the MDB on the server (clients
preference). If problems occur moving it to the work-
station may be the go, then MSDE.

I'd suggest a separate copy for each user, of the database used to query the
data, linking the tables in the server-side MDB. That's true even with
Terminal Server, each user should have his/her own copy of the front-end.

Larry Linson
Microsoft Access MVP

[Jeff Pritchard]

They already do.

Each user has a separate copy of the front-end MDE. The actual database is 2
different front-end MDE files (different functionality for different areas),
separate MDEs for each user, linking to 3 different back-end MDB files.
Back-end files contain separate sets of data that don't have any
relationship to each other, or where there is no complication with
relationships (cascades etc).

The whole system works beautifully. This checkweigher stuff is a new
requirement. There will be a new back-end MDB for the checkweigher data.

Thanks for your comments Larry.

Jeff

"Larry Linson" <bo*****@localhost.not> wrote in message
news:rx******************@nwrddc03.gnilink.net...

"Jeff Pritchard" wrote

> MSDE is a definite option if the MDB
> solution has a problem. I think we will
> start with the MDB on the server (clients
> preference).

> If problems occur moving it to the work-
> station may be the go, then MSDE.

I'd suggest a separate copy for each user, of the database used to query

the data, linking the tables in the server-side MDB. That's true even with
Terminal Server, each user should have his/her own copy of the front-end.

Larry Linson
Microsoft Access MVP

[Jeff Pritchard]

I tried that test over my PTP network and wrote 100,000 records in 10
seconds. Tried a few other tests and speed is not a problem. Worked up to
500,000 records.

Jeff

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:pv********************************@4ax.com...

On Sat, 15 Nov 2003 01:54:12 GMT, "Jeff Pritchard"
<je************@asken.com.au> wrote:

A peak append rate of 3 - 5 records (or higher?) per second is rather
high. I would take an hour or so to write a simple proof-of-concept
app to make sure your system can handle that. Tick a timer every 200
msec and append a row. Then add 100,000 rows to that table and try
again.

I'm not that concerned about the SELECT queries. The power of indexing
should ensure it hardly matters whether you're querying 100 rows or
100,000. Except for some increased network traffic. You could even
have those guys hitting a separate database, which you populate every
so often with a data dump from the first database.

Access IMHO is not a 24/7 database. I would lean towards a SQL Server
database for this.

-Tom.

I have an MDB that contains a single table. Checkweigher data is being
continuously written to this MDB from a dedicated workstation over a networkinto the MDB on the server at the rate of about 120 records a minute. Thismay increase in the future to about 200. The records are not large.

We have other workstations that run sessions on Terminal Server querying
this MDB, doing things like plotting the last hours data on a graph or
running other reports, or even just viewing raw data. No one else will be
writing to the MDB.

Can anyone see any potential problems.

I am concerned with the network connection being lost during the writing andhaving a corrupted MDB. The issue of speed is another. We expect 70-80,000records a day which means that there could be a substantial delay in
querying towards the end of the day. The MDB will be archived each day andstart empty the next day.

I don't see any locking issues.

Jeff